﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Rezepto.DatenbankObjekte;
using Rezepto.Enum;

namespace Rezepto.DatenbankVerbindung
{
    public static class MsSqlVerbindung
    {
        private static SqlConnection MsSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MsSqlRezeptConnectionString"].ConnectionString);
        private static SqlCommand _sqlCommand;

        static void OpenConnection()
        {
            if (MsSqlConnection.State  != ConnectionState.Open)
            {
                MsSqlConnection.Open();
            }
        }

        static void CloseConnection()
        {
            MsSqlConnection.Close();
        }

        public static Rezept LeseRezept(decimal rezeptId)
        {
            if (rezeptId == 0)
            {
                return null;
            }
            var sqlString =
              @"SELECT RezeptId, Benutzername, Kalorien, Rezeptname, KategorieId, Zubereitung,
                (Select Round(AVG(Bewertung), 2) Bewertung from Bewertung where bewertung.rezeptid = @RezeptId ) as Bewertung
                FROM Rezept join Benutzer on Rezept.BenutzerID = benutzer.BenutzerID Where Rezept.rezeptid = @RezeptId";
            OpenConnection();
            _sqlCommand = MsSqlConnection.CreateCommand();
            
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("RezeptId",rezeptId);
            var reader = _sqlCommand.ExecuteReader();

            Rezept rezept = null;
            while (reader.Read())
            {
                rezept = new Rezept();
                rezept.RezeptId = (int)reader["RezeptId"];
                rezept.Benutzername = reader["Benutzername"].ToString();
                rezept.Kalorien = (decimal)reader["Kalorien"];
                rezept.RezeptName = reader["Rezeptname"].ToString();
                rezept.Kategorie = (Kategorie) (int) reader["KategorieId"];
                rezept.Bewertung = reader["Bewertung"] == DBNull.Value ? 0 : (decimal)reader["Bewertung"];
                rezept.Zubereitung = reader["Zubereitung"].ToString();
                rezept.Bilder.AddRange(GetAllImagesFromRecipe(rezept.RezeptId));
                rezept.Kommentare.AddRange(GetAllCommentsFromRecipe(rezept.RezeptId));
                rezept.Zutaten.AddRange(GetAllIngridientsFromRecipe(rezept.RezeptId));
            }
            CloseConnection();
            return rezept;
        }

        public static List<Rezept> SucheRezepte(string sucheAnfrage)
        {
            var rezeptList = new List<Rezept>();

            if (sucheAnfrage == string.Empty)
            {
                return rezeptList;
            }
            var sqlString =
              @"SELECT Rezept.RezeptId, Kalorien, Rezeptname, KategorieId, Zubereitung, Round(AVG(Bewertung),2) Bewertung
                FROM Rezept  left join Bewertung on Rezept.rezeptid = bewertung.rezeptid Where Lower(Rezeptname) like @SuchAnfrage
                group by rezept.rezeptid, kalorien, rezeptname, kategorieId, Zubereitung";
            OpenConnection();
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("@SuchAnfrage", string.Format("%{0}%",sucheAnfrage.ToLower()));
            var reader = _sqlCommand.ExecuteReader();
            
            while (reader.Read())
            {
                var rezept = new Rezept();
                rezept.RezeptId = (int)reader["RezeptId"];
                rezept.Kalorien = (decimal)reader["Kalorien"];
                rezept.RezeptName = reader["Rezeptname"].ToString();
                rezept.Kategorie = (Kategorie)(int)reader["KategorieId"];
                rezept.Bewertung = reader["Bewertung"] == DBNull.Value ? 0 : (decimal) reader["Bewertung"];
                rezept.Zubereitung = reader["Zubereitung"].ToString();
                rezept.Bilder.AddRange(GetAllImagesFromRecipe(rezept.RezeptId));
                rezeptList.Add(rezept);
            }
            CloseConnection();
            return rezeptList;
        }

        public static IEnumerable<byte[]> GetAllImagesFromRecipe(decimal rezeptId)
        {
            var imageList = new List<byte[]>();
            var sqlString = @"SELECT Bild from Bild where Rezeptid = @RezeptId";
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("RezeptId", rezeptId);
            var reader = _sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                imageList.Add((byte[])reader["Bild"]);
            }
            return imageList;
        }

        private static IEnumerable<Zutat> GetAllIngridientsFromRecipe(decimal rezeptId)
        {
            var ingridients = new List<Zutat>();
            var sqlString =
                @"SELECT Menge, Zutat, Einheit From Zutat where Rezeptid = @RezeptId";
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("RezeptId", rezeptId);
            var reader = _sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                ingridients.Add(new Zutat
                {
                    Menge = (decimal)reader["Menge"],
                    ZutatName = reader["Zutat"].ToString(),
                    Einheit = reader["Einheit"].ToString(),
                });
            }
            return ingridients;
        }

        private static IEnumerable<Kommentar> GetAllCommentsFromRecipe(decimal rezeptId)
        {
            var comments = new List<Kommentar>();
            var sqlString =
            @"SELECT Kommentar, Benutzername, Datum from Kommentar 
              join Benutzer on Kommentar.BenutzerID = benutzer.BenutzerID where Rezeptid = @RezeptId order by Datum DESC";
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("RezeptId", rezeptId);
            var reader = _sqlCommand.ExecuteReader();
            while (reader.Read())
            {
                comments.Add(new Kommentar
                {
                    Datum = (DateTime) reader["Datum"],
                    KommentarText = reader["Kommentar"].ToString(),
                    Benutzername = reader["Benutzername"].ToString()
                });
            }
            return comments;
        }

        public static decimal GetNextSequenceForTable(string tabellenname)
        {
            var sqlstring = "select Max("+ tabellenname+"id) from " + tabellenname;
            OpenConnection();
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlstring;
            var id = _sqlCommand.ExecuteScalar();
            if (id == DBNull.Value)
            {
                id = 0;
            }
            CloseConnection();
            return (int) id+1;
        }
        
        public static void SpeicherBenutzer(Benutzer benutzerToAdd)
        {
            var sqlString =
                @"Insert into Benutzer (BenutzerID, VORNAME, NACHNAME, BENUTZERNAME, EMAIL, ROLLENID, PASSWORT) 
                              Values (@BenutzerID, @vorname, @nachname, @benutzername, @email, @rollenid, @password)";
            OpenConnection();
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;

            _sqlCommand.Parameters.AddWithValue("BenutzerID", benutzerToAdd.Id);
            _sqlCommand.Parameters.AddWithValue("vorname", benutzerToAdd.Vorname);
            _sqlCommand.Parameters.AddWithValue("nachname", benutzerToAdd.Nachname);
            _sqlCommand.Parameters.AddWithValue("benutzername", benutzerToAdd.Benutzername);
            _sqlCommand.Parameters.AddWithValue("email", benutzerToAdd.Email);
            _sqlCommand.Parameters.AddWithValue("rollenid", (int)benutzerToAdd.Rolle);
            _sqlCommand.Parameters.AddWithValue("password", benutzerToAdd.Password);
            
            _sqlCommand.ExecuteNonQuery();
            CloseConnection();
        }

        public static bool UserExist(string benutzerName)
        {
            var sqlString =
                @"Select count(BenutzerID) FROM Benutzer where BenutzerName = @benutzername";
            OpenConnection();
            _sqlCommand = MsSqlConnection.CreateCommand();
            _sqlCommand.CommandText = sqlString;
            _sqlCommand.Parameters.AddWithValue("benutzername", benutzerName);
            var count = (int)_sqlCommand.ExecuteScalar();
            CloseConnection();
            return count > 0;
        }

        public static Benutzer LoginUser(string benutzerName, string passwort)
        {
            try
            {
                var sqlString =
                    @"Select BenutzerID, Benutzername,Vorname,Nachname,Email,RollenID,Aktiv FROM Benutzer where BenutzerName = @benutzername and Passwort = @passwort";
                OpenConnection();
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.Parameters.AddWithValue("benutzername", benutzerName);
                _sqlCommand.Parameters.AddWithValue("passwort", passwort);
                var reader = _sqlCommand.ExecuteReader();
                Benutzer benutzer = null;
                while (reader.Read())
                {
                    benutzer = new Benutzer();
                    benutzer.Id = Convert.ToInt16(reader["BenutzerID"]);
                    benutzer.Benutzername = reader["Benutzername"].ToString();
                    benutzer.Vorname = reader["Vorname"].ToString();
                    benutzer.Nachname = reader["Nachname"].ToString();
                    benutzer.Email = reader["Email"].ToString();
                    benutzer.Rolle = (Rolle) (int) reader["RollenID"];
                    var aktiv = Convert.ToInt16(reader["Aktiv"]);
                    benutzer.IsAktiv = aktiv == 1;
                }
                CloseConnection();
                return benutzer;
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }

        public static List<Benutzer> GetAllUsers()
        {
            try
            {
                var sqlString =
                    @"Select BenutzerID,Benutzername,Vorname,Nachname,Email,RollenID,Aktiv FROM Benutzer";
                OpenConnection();
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.CommandText = sqlString;
                var reader = _sqlCommand.ExecuteReader();
                var benutzerListe = new List<Benutzer>();
                while (reader.Read())
                {
                    var benutzer = new Benutzer();
                    benutzer.Id = (int) reader["BenutzerID"];
                    benutzer.Benutzername = reader["Benutzername"].ToString();
                    benutzer.Vorname = reader["Vorname"].ToString();
                    benutzer.Nachname = reader["Nachname"].ToString();
                    benutzer.Email = reader["Email"].ToString();
                    benutzer.Rolle = (Rolle)(int)reader["RollenID"];
                    var aktiv = Convert.ToInt16(reader["Aktiv"]);
                    benutzer.IsAktiv = aktiv == 1;
                    benutzerListe.Add(benutzer);
                }
                CloseConnection();
                return benutzerListe;
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }

        public static void UserAktivierenDeaktivieren(int benutzerID, int aktiv)
        {
            try
            {
                
                OpenConnection();
                var sqlString =
                    @"Update Benutzer Set Aktiv = @aktiv where BenutzerID = @BenutzerID"; 
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.Parameters.AddWithValue("aktiv", aktiv);
                _sqlCommand.Parameters.AddWithValue("BenutzerID", benutzerID);
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }

        public static void UpdateBenutzerDaten(Benutzer changedUser)
        {
            try
            {

                OpenConnection();
                var sqlString =
                    @"Update Benutzer set VORNAME = @vorname, NACHNAME = @nachname, EMAIL = @email, PASSWORT = @password where Benutzername = @benutzername";
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.Parameters.AddWithValue("vorname", changedUser.Vorname);
                _sqlCommand.Parameters.AddWithValue("nachname", changedUser.Nachname);
                _sqlCommand.Parameters.AddWithValue("email", changedUser.Email);
                _sqlCommand.Parameters.AddWithValue("password", changedUser.Password);
                _sqlCommand.Parameters.AddWithValue("benutzername", changedUser.Benutzername);
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }

        public static void SpeicherRezept(int kategorieId, byte[] bild, string beschreibungString, List<Zutat> zutaten, decimal kalorien, decimal rezeptId, decimal zutatId, decimal benutzerID, String rezeptName, decimal bildId)
        {
            try
            {
                OpenConnection();
                //Insert in die Tabelle Rezept
                var sqlString =
                    @"Insert Into Rezept (rezeptid, BenutzerID, kalorien, rezeptname, kategorieid, zubereitung) 
                                   values(@rezeptid, @BenutzerID, @kalorien, @rezeptname, @kategorieid, @zubereitung)";
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.Parameters.AddWithValue("rezeptid", rezeptId);
                _sqlCommand.Parameters.AddWithValue("BenutzerID", benutzerID);
                _sqlCommand.Parameters.AddWithValue("kalorien", kalorien);
                _sqlCommand.Parameters.AddWithValue("rezeptname", rezeptName);
                _sqlCommand.Parameters.AddWithValue("kategorieid", kategorieId);
                _sqlCommand.Parameters.AddWithValue("zubereitung", beschreibungString);
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
                //Bild zum Rezept speichern, falls eins ausgewählt wurde
                if(bild.Length>0)
                    SpeicherBild(bild,bildId,rezeptId,benutzerID);
                //Zutaten zum Rezept speichern
                SpeicherZutaten(rezeptId,zutaten);
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }
        public static void SpeicherBild(Byte[] img, decimal bildId, decimal rezeptId,decimal benutzerID)
        {
            try
            {
                var sqlString =
                    @"Insert Into BILD (BILDID, REZEPTID, BILD, BenutzerID)  
                                   Values(@bildId,@rezeptId,@BILD,@BenutzerID)";
                OpenConnection();
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.Parameters.AddWithValue("bildId", bildId);
                _sqlCommand.Parameters.AddWithValue("rezeptId", rezeptId);
                _sqlCommand.Parameters.AddWithValue("BILD" , img);
                _sqlCommand.Parameters.AddWithValue("BenutzerID", benutzerID);
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }

        }

        public static void SpeicherZutaten(decimal rezeptId, List<Zutat> zutaten)
        {
            try
            {
                foreach(var zutat in zutaten)
                {
                    decimal zutatId = GetNextSequenceForTable("ZUTAT");
                    OpenConnection();
                    var sqlString =
                          @"Insert Into ZUTAT (ZUTATID, REZEPTID, MENGE, ZUTAT, EINHEIT)  
                                       Values(@zutatId, @rezeptId, @menge, @zutat, @einheit)";
                    _sqlCommand = MsSqlConnection.CreateCommand();
                    _sqlCommand.CommandText = sqlString;
                    _sqlCommand.Parameters.AddWithValue("zutatId", zutatId);
                    _sqlCommand.Parameters.AddWithValue("rezeptId", rezeptId);
                    _sqlCommand.Parameters.AddWithValue("Menge", zutat.Menge);
                    _sqlCommand.Parameters.AddWithValue("zutat", zutat.ZutatName);
                    _sqlCommand.Parameters.AddWithValue("einheit", zutat.Einheit);
                    _sqlCommand.ExecuteNonQuery();
                    CloseConnection();
                }
                
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }

        }

        public static void SaveCommentary(Kommentar kommentar)
        {
            try
            {
                OpenConnection();
                var sqlString =
                        @"Insert Into Kommentar (KommentarId, RezeptId, BenutzerID, Kommentar, Datum)  
                                    Values(@KommentarId, @RezeptId, @BenutzerID, @Kommentar, @Datum)";
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.Parameters.AddWithValue("KommentarId", kommentar.KommentarId);
                _sqlCommand.Parameters.AddWithValue("RezeptId", kommentar.RezeptId);
                _sqlCommand.Parameters.AddWithValue("BenutzerID", kommentar.UserId);
                _sqlCommand.Parameters.AddWithValue("Kommentar", kommentar.KommentarText);
                _sqlCommand.Parameters.AddWithValue("Datum", kommentar.Datum);
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
            }
            catch (Exception exception)
            {
                throw new Exception("Ups da ist ein Fehler aufgetreten", exception);
            }
            finally
            {
                CloseConnection();
            }
        }

        public static bool UpdateBewertungForRecipe(decimal rezeptId, decimal rating, decimal benutzerID)
        {
            try
            {
                var bewertungsId = GetNextSequenceForTable("BEWERTUNG");
                OpenConnection();
                var sqlString =
                        @"Insert Into Bewertung (BEWERTUNGID, REZEPTID, BenutzerID, BEWERTUNG)  
                                    Values(@BEWERTUNGID, @REZEPTID, @BenutzerID, @BEWERTUNG)";
                _sqlCommand = MsSqlConnection.CreateCommand();
                _sqlCommand.CommandText = sqlString;
                _sqlCommand.Parameters.AddWithValue("BEWERTUNGID", bewertungsId);
                _sqlCommand.Parameters.AddWithValue("REZEPTID", rezeptId);
                _sqlCommand.Parameters.AddWithValue("BenutzerID", benutzerID);
                _sqlCommand.Parameters.AddWithValue("BEWERTUNG", rating);
                _sqlCommand.ExecuteNonQuery();
                CloseConnection();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                CloseConnection();
            }
        }
    }
}